# -*- coding: utf-8 -*-
"""
-------------------------------------------------
   File Name：     ALPHA101part3(industry)
   Description :
   Author :       Administrator
   date：          2019/4/15
-------------------------------------------------
   Change Activity:
                   2019/4/15:
-------------------------------------------------
"""
__author__ = 'Administrator'

import numpy as np
import pandas as pd
from sqlalchemy import create_engine
import datetime as dt
from sqlalchemy import types
import re
con = create_engine('mssql+pymssql://QRUser04:Passw0rdBP@10.86.166.13/BP_New_Factor')
conTL = create_engine('mssql+pymssql://QRUser04:Passw0rdBP@10.86.166.13/TLDB')

Today = dt.datetime.strftime(dt.datetime.today(), '%Y-%m-%d')
TLDB_Latest_Date = pd.read_sql('SELECT TOP (1) TRADE_DATE \
                               FROM TLDB.dbo.mkt_equd \
                               ORDER BY TRADE_DATE DESC', conTL).iloc[0, 0]
Factor_Latest_Date = pd.read_sql('SELECT TOP (1) TRADE_DATE \
                               FROM BP_New_Factor.dbo.ALPHA059 \
                               ORDER BY TRADE_DATE DESC', con).iloc[0, 0]
Factor_230_Date = pd.read_sql('SELECT TOP (260) TRADE_DATE \
                               FROM BP_New_Factor.dbo.ALPHA059 \
                               ORDER BY TICKER_SYMBOL,TRADE_DATE DESC', con).iloc[-1, 0]

# %% 定义基本函数

def rolling_corr(df1, df2, window):
    if df1.shape[0] != df2.shape[0]:
        df1 = df1.reindex(df2.index)
    return df1.rolling(window).corr(df2)


def rolling_rank(df1, window, percent=True):
    rank_list = []
    for start in range(df1.shape[0] - window + 1):
        end = start + window
        x = df1.iloc[start:end]
        rank = x.rank(axis=0, pct=percent).iloc[-1]
        rank_list.append(rank)
    rank_df = pd.concat(rank_list, axis=1).T.set_index(df1.index[window - 1:])
    return rank_df


def scale(df, n=1):
    return n * df.div(abs(df).sum(axis=1), axis=0)


def decay_linear(df, window):
    weight = np.array(range(1, window + 1))
    result_list = []
    for start in range(df.shape[0] - window + 1):
        end = start + window
        x = df.values[start:end]
        weighted_mean = np.average(x, axis=0, weights=weight)
        result_list.append(weighted_mean)
    result_df = pd.DataFrame(result_list, columns=df.columns).set_index(df.index[window - 1:])
    return result_df


def ind_neutralize(df, industry):
    temp = pd.concat([df, industry], axis=1).reindex(df.index).set_index('SW1', append=True)
    result = temp - temp.groupby(level=[0, 2]).transform('mean')
    return result.reset_index(level=-1, drop=True).iloc[:,-1].unstack()



# %% 获取后复权数据（包含vwap，2009年开始）
vmarket_af = pd.read_sql("SELECT a.TRADE_DATE, a.TICKER_SYMBOL, \
                       a.OPEN_PRICE_2 as 'open', a.HIGHEST_PRICE_2 as high, \
                       a.LOWEST_PRICE_2 as low, a.CLOSE_PRICE_2 as 'close', \
                       (b.TURNOVER_VOL / a.ACCUM_ADJ_FACTOR_2) as volume, \
                       b.NEG_MARKET_VALUE as nmkt, (v.VWAP * a.ACCUM_ADJ_FACTOR_2) as vwap \
                       FROM TLDB.dbo.mkt_equd_adj_af AS a, TLDB.dbo.mkt_equd AS b, TLDB.dbo.mkt_equ_vwapd as v \
                       WHERE a.SECURITY_ID = b.SECURITY_ID \
                       and a.SECURITY_ID = v.SECURITY_ID \
                       and a.TRADE_DATE = b.TRADE_DATE \
                       and a.TRADE_DATE = v.TRADE_DATE \
                       and a.TRADE_DATE > '%s' \
                       and a.TRADE_DATE <= '%s' \
                       ORDER by TICKER_SYMBOL, TRADE_DATE" % (Factor_230_Date, TLDB_Latest_Date), conTL,
                         index_col=['TRADE_DATE', 'TICKER_SYMBOL'])

vopen_af = vmarket_af['open'].unstack()
vclose_af = vmarket_af['close'].unstack()
vhigh_af = vmarket_af['high'].unstack()
vlow_af = vmarket_af['low'].unstack()
vvolume_af = vmarket_af['volume'].unstack()
vnmkt = vmarket_af['nmkt'].unstack()
vwap_af = vmarket_af['vwap'].unstack()
vret = vclose_af.pct_change()
#%%
# 获取行业分类数据
industry = pd.read_sql("SELECT dbo.mkt_equd.TRADE_DATE, dbo.mkt_equd.TICKER_SYMBOL, \
                        SUBSTRING( dbo.md_inst_type.TYPE_ID, 7, 2 ) AS SW1 \
                        FROM dbo.md_security, dbo.md_inst_type, dbo.mkt_equd\
                        where dbo.md_security.SECURITY_ID = dbo.mkt_equd.SECURITY_ID \
                        and dbo.md_security.PARTY_ID = dbo.md_inst_type.PARTY_ID \
                        and SUBSTRING( dbo.md_inst_type.TYPE_ID, 1, 6 ) = 010303 \
                        AND TRADE_DATE > '%s' \
                        AND TRADE_DATE <= '%s' \
                        AND TRADE_DATE >= INTO_DATE \
                        AND ( TRADE_DATE < OUT_DATE OR ( OUT_DATE IS NULL ) ) \
                        " % (Factor_230_Date, TLDB_Latest_Date), conTL,
                        index_col=['TRADE_DATE', 'TICKER_SYMBOL'])['SW1']

# %%
print(dt.datetime.now())
# alpha059:
# (-1 * Ts_Rank(decay_linear(correlation(IndNeutralize(((vwap * 0.728317) + (vwap *(1 - 0.728317))), IndClass.industry), volume, 4.25197), 16.2289), 8.19648))
vwap_ind = ind_neutralize(vmarket_af['vwap'], industry)
a = rolling_corr(vwap_ind, vvolume_af, 4)
ALPHA059 = -rolling_rank(decay_linear(a, 16), 8)

# alpha063:
# ((rank(decay_linear(delta(IndNeutralize(close, IndClass.industry), 2.25164), 8.22237))- rank(decay_linear(correlation(((vwap * 0.318108) + (open * (1 - 0.318108))), sum(adv180,37.2467), 13.557), 12.2883))) * -1)
close_ind = ind_neutralize(vmarket_af['close'], industry)
adv180 = vvolume_af.rolling(180).mean()
a = close_ind - close_ind.shift(2)
x = decay_linear(a, 8).rank(axis=1, pct=True)
b = vwap_af * 0.318108 + vopen_af * (1 - 0.318108)
c = adv180.rolling(37).sum()
y = decay_linear(rolling_corr(b, c, 13), 12).rank(axis=1, pct=True)
ALPHA063 = -(x - y)

# alpha069:
# ((rank(ts_max(delta(IndNeutralize(vwap, IndClass.industry), 2.72412),4.79344))^Ts_Rank(correlation(((close * 0.490655) + (vwap * (1 - 0.490655))), adv20, 4.92416),9.0615)) * -1)
adv20 = vvolume_af.rolling(20).mean()
a = vwap_ind - vwap_ind.shift(2)
x = a.rolling(4).max().rank(axis=1, pct=True)
b = vclose_af * 0.490655 + vwap_af * (1 - 0.490655)
y = rolling_rank(rolling_corr(b, adv20, 4), 9)
ALPHA069 = -x ** y

# alpha070:
# ((rank(delta(vwap, 1.29456))^Ts_Rank(correlation(IndNeutralize(close,IndClass.industry), adv50, 17.8256), 17.9171)) * -1)
adv50 = vvolume_af.rolling(50).mean()
x = (vwap_af - vwap_af.shift(1)).rank(axis=1, pct=True)
a = rolling_corr(close_ind, adv50, 17)
y = rolling_rank(a, 17)
ALPHA070 = -x ** y

# alpha080
# ((rank(Sign(delta(IndNeutralize(((open * 0.868128) + (high * (1 - 0.868128))),IndClass.industry), 4.04545)))^Ts_Rank(correlation(high, adv10, 5.11456), 5.53756)) * -1)
adv10 = vvolume_af.rolling(10).mean()
a = (vopen_af * 0.868128 + vhigh_af * (1 - 0.868128)).stack()
b = ind_neutralize(a, industry)
x = np.sign(b - b.shift(4)).rank(axis=1, pct=True)
c = rolling_corr(vhigh_af, adv10, 5)
y = rolling_rank(c, 5)
ALPHA080 = -x ** y

# alpha087
# (max(rank(decay_linear(delta(((close * 0.369701) + (vwap * (1 - 0.369701))),1.91233), 2.65461)), Ts_Rank(decay_linear(abs(correlation(IndNeutralize(adv81,IndClass.industry), close, 13.4132)), 4.89768), 14.4535)) * -1)
adv81 = vvolume_af.rolling(81).mean().stack()
a = vclose_af * 0.369701 + vwap_af * (1 - 0.369701)
x = decay_linear(a - a.shift(1), 2).rank(axis=1, pct=True)
b = ind_neutralize(adv81, industry)
c = abs(rolling_corr(b, vclose_af, 13))
y = rolling_rank(decay_linear(c, 4), 14).reindex(x.index)
ALPHA087 = -x.where(x > y, y)

# alpha089
# (Ts_Rank(decay_linear(correlation(((low * 0.967285) + (low * (1 - 0.967285))), adv10,6.94279), 5.51607), 3.79744) - Ts_Rank(decay_linear(delta(IndNeutralize(vwap,IndClass.industry), 3.48158), 10.1466), 15.3012))
adv10 = vvolume_af.rolling(10).mean()
a = rolling_corr(vlow_af, adv10, 6)
x = rolling_rank(decay_linear(a, 5), 3)
b = vwap_ind - vwap_af.shift(3)
y = rolling_rank(decay_linear(b, 10), 15)
ALPHA089 = x - y

# alpha091:
# ((Ts_Rank(decay_linear(decay_linear(correlation(IndNeutralize(close,IndClass.industry), volume, 9.74928), 16.398), 3.83219), 4.8667) -rank(decay_linear(correlation(vwap, adv30, 4.01303), 2.6809))) * -1)
adv30 = vvolume_af.rolling(30).mean()
a = rolling_corr(close_ind, vvolume_af, 9)
x = rolling_rank(decay_linear(decay_linear(a, 16), 3), 4)
b = rolling_corr(vwap_af, adv30, 4)
y = decay_linear(b, 2).rank(axis=1, pct=True)
ALPHA091 = y - x

# alpha093:
# (Ts_Rank(decay_linear(correlation(IndNeutralize(vwap, IndClass.industry), adv81,17.4193), 19.848), 7.54455) / rank(decay_linear(delta(((close * 0.524434) + (vwap * (1 -0.524434))), 2.77377), 16.2664)))
adv81 = vvolume_af.rolling(81).mean()
a = rolling_corr(vwap_ind, adv81, 17)
x = rolling_rank(decay_linear(a, 19), 7)
b = vclose_af * 0.524434 + vwap_af * (1 - 0.524434)
y = decay_linear(b - b.shift(2), 16).rank(axis=1, pct=True)
ALPHA093 = x / y

# alpha097:
# ((rank(decay_linear(delta(IndNeutralize(((low * 0.721001) + (vwap * (1 - 0.721001))),IndClass.industry), 3.3705), 20.4523)) - Ts_Rank(decay_linear(Ts_Rank(correlation(Ts_Rank(low,7.87871), Ts_Rank(adv60, 17.255), 4.97547), 18.5925), 15.7152), 6.71659)) * -1)
# adv60 = vvolume_af.rolling(60).mean()
# a = vlow_af * 0.721001 + vwap_af * (1 - 0.721001)
# b = ind_neutralize(a.stack(), industry)
# x = decay_linear(b - b.shift(3), 20).rank(axis=1, pct=True)
# c = rolling_rank(vlow_af, 7)
# d = rolling_rank(adv60, 17)
# e = rolling_corr(c, d, 4)
# f = rolling_rank(e, 18)
# y = rolling_rank(decay_linear(f, 15), 6)
# ALPHA097 = y - x

# 板块中性化和子行业中性化替换为行业中性化

# alpha048
# (indneutralize(((correlation(delta(close, 1), delta(delay(close, 1), 1), 250) *delta(close, 1)) / close), IndClass.subindustry) / sum(((delta(close, 1) / delay(close, 1))^2), 250))
a = vclose_af - vclose_af.shift()
b = rolling_corr(a, a.shift(), 250) * a / vclose_af
x = ind_neutralize(b.stack(),industry)
c = (a / vclose_af.shift())**2
y = c.rolling(250).sum()
ALPHA048 = x / y

# alpha058
# (-1 * Ts_Rank(decay_linear(correlation(IndNeutralize(vwap, IndClass.sector), volume,3.92795), 7.89291), 5.50322))
a = rolling_corr(vwap_ind, vvolume_af, 3)
ALPHA058 = -rolling_rank(decay_linear(a, 7), 5)

# alpha067
# ((rank((high - ts_min(high, 2.14593)))^rank(correlation(IndNeutralize(vwap,IndClass.sector), IndNeutralize(adv20, IndClass.subindustry), 6.02936))) * -1)
adv20 = vvolume_af.rolling(20).mean()
adv20_ind = ind_neutralize(adv20.stack(),industry)
a = vhigh_af - vhigh_af.rolling(2).min()
x = a.rank(axis=1, pct=True)
b = rolling_corr(vwap_ind, adv20_ind, 6)
y = b.rank(axis=1, pct=True)
ALPHA067 = -x ** y

# alpha076
# (max(rank(decay_linear(delta(vwap, 1.24383), 11.8259)),Ts_Rank(decay_linear(Ts_Rank(correlation(IndNeutralize(low, IndClass.sector), adv81,8.14941), 19.569), 17.1543), 19.383)) * -1)
adv81 = vvolume_af.rolling(81).mean()
low_ind = ind_neutralize(vmarket_af['low'], industry)
a = vwap_af - vwap_af.shift()
x = decay_linear(a,11).rank(axis=1, pct=True)
b = rolling_corr(low_ind, adv81, 8)
c = rolling_rank(b, 19)
y = rolling_rank(decay_linear(c,17),19).reindex(x.index)
ALPHA076 = x.where(x>y, y)

# alpha079
# (rank(delta(IndNeutralize(((close * 0.60733) + (open * (1 - 0.60733))),IndClass.sector), 1.23438)) < rank(correlation(Ts_Rank(vwap, 3.60973), Ts_Rank(adv150,9.18637), 14.6644)))
adv150 = vvolume_af.rolling(150).mean()
a = vclose_af * 0.60733 + vopen_af * (1 - 0.60733)
a_ind = ind_neutralize(a.stack(), industry)
x = (a_ind - a_ind.shift()).rank(axis=1, pct=True)
b = rolling_rank(vwap_af, 3)
c = rolling_rank(adv150, 9)
y = rolling_corr(b,c,14).rank(axis=1, pct=True).reindex(x.index)
ALPHA079 = (x < y).where(~y.isna())

# alpha082
# (min(rank(decay_linear(delta(open, 1.46063), 14.8717)),Ts_Rank(decay_linear(correlation(IndNeutralize(volume, IndClass.sector), ((open * 0.634196) +(open * (1 - 0.634196))), 17.4842), 6.92131), 13.4283)) * -1)
a = vopen_af- vopen_af.shift()
x = decay_linear(a, 14).rank(axis=1, pct=True)
volume_ind = ind_neutralize(vmarket_af['volume'],industry)
b = rolling_corr(volume_ind, vopen_af, 17)
y = rolling_rank(decay_linear(b, 6),13).reindex(x.index)
ALPHA082 = x.where(x<y, y)

# alpha090
# ((rank((close - ts_max(close, 4.66719)))^Ts_Rank(correlation(IndNeutralize(adv40,IndClass.subindustry), low, 5.38375), 3.21856)) * -1)
adv40 = vvolume_af.rolling(40).mean()
adv40_ind = ind_neutralize(adv40.stack(), industry)
x = (vclose_af - vclose_af.rolling(4).max()).rank(axis=1, pct=True)
b = rolling_corr(adv40_ind, vlow_af, 5)
y = rolling_rank(b, 3)
ALPHA090 = -x ** y

# alpha100
# (0 - (1 * (((1.5 * scale(indneutralize(indneutralize(rank(((((close - low) - (high -close)) / (high - low)) * volume)), IndClass.subindustry), IndClass.subindustry))) -scale(indneutralize((correlation(close, rank(adv20), 5) - rank(ts_argmin(close, 30))),IndClass.subindustry))) * (volume / adv20))))
# adv20 = vvolume_af.rolling(20).mean()
# a = 2 * vclose_af - vlow_af -vhigh_af
# b = vhigh_af - vlow_af
# c = (a /b * vvolume_af).rank(axis=1, pct=True)
# c_ind = ind_neutralize(c.stack(), industry)
# d = rolling_corr(vclose_af, adv20.rank(axis=1, pct=True), 5)
# e = vclose_af.rolling(30).apply(np.argmin).rank(axis=1, pct=True)
# d_e_ind = ind_neutralize((d-e).stack(),industry)
# x = 1.5 * scale(ind_neutralize(c_ind.stack(), industry))
# y = scale(d_e_ind)
# ALPHA100 = -(x - y) * vvolume_af / adv20
print(dt.datetime.now())

#%%
variable = ' '.join(dir())
talist = re.findall(r'\b[A-Z][0-9A-Z_]*\b', variable)

print(dt.datetime.now())
with pd.option_context('mode.use_inf_as_na', True):
    for indi in talist:
        indi_df = eval(indi)[eval(indi).index > Factor_Latest_Date].stack().rename(indi).reset_index()
        # indi_df.to_csv('%s.csv' % indi, index=False)
        indi_df.to_sql(indi, con, index=False, dtype={'TRADE_DATE': types.DATE}, if_exists='append')
print(dt.datetime.now())

